package com.cx.dbtablelist.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.cx.dbtablelist.ProjectConfig;
import com.cx.dbtablelist.util.ExpressionUtils;
import com.cx.dbtablelist.util.RegexUtils;

public abstract class DBUtils {
	private static Logger logger = Logger.getLogger(DBUtils.class.getName());
	
	
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
	
	{
	//加载数据库驱动
		try {
			Class.forName(ProjectConfig.get("DRIVE_NAME"));
		} catch (Exception e) {
			logger.log(Level.WARNING,e.getMessage());
		}
	}
	
	public static Connection getConnection() throws SQLException{
		if(threadLocal.get() == null){
			threadLocal.set(DriverManager.getConnection(ProjectConfig.get("DB_URL"),
					ProjectConfig.get("USER_NAME"), ProjectConfig.get("PASSWORD")));
		}
		return threadLocal.get();
	}
	
	public static PreparedStatement getPreparedStatement(String sql) throws SQLException{
		Connection con = getConnection();
		return con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	}
	
	public static ResultSet executeSQL(String sql, List<Object> params) throws SQLException{
		PreparedStatement preparedStatement = getPreparedStatement(sql);
		if(params != null && !params.isEmpty()){
			for (int i=0 ; i < params.size(); i++) {
				preparedStatement.setObject(i+1, params.get(i));
			}
		}
		return preparedStatement.executeQuery();
	}
	
	public static ResultSetMetaData getMetaData(String sql, List<Object> params) throws SQLException{
		return executeSQL(sql, params).getMetaData();
	}
	
	/**
	 * 得到相关表的信息
	 * @author caoxu-yiyang@qq.com
	 * @param tables 表名
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet getTableInfo(ResultSet tables) throws SQLException{
		String contentSQL = ProjectConfig.get("CONTENT_SQL");
		
		List<String> paramsInSQL = RegexUtils.match(contentSQL, ExpressionUtils.REGEX_REXPRESS);
		List<Object> params = new ArrayList<Object>();
		for (String paramNotation : paramsInSQL) {
			String paramsValue =tables.getString(ExpressionUtils.getExpressionValue(paramNotation))+"";
			params.add(paramsValue);
		}
		logger.info(contentSQL);
		return executeSQL(contentSQL.replaceAll(ExpressionUtils.REGEX_REXPRESS, "?"), params);
	}
	
	public static ResultSet getTables() throws SQLException{
		logger.info(ProjectConfig.get("TABLE_SQL"));
		return executeSQL(ProjectConfig.get("TABLE_SQL"),null);
	}
}
